Software Vault: The Gold Collection

home *** CD-ROM | disk | FTP | other *** search

/ Software Vault: The Gold Collection / Software Vault - The Gold Collection (American Databankers) (1993).ISO / cdr11 / pdox693.zip / TI1112.ASC < prev next >

Wrap

Text File | 1992-08-25 | 16KB | 661 lines

PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 1/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. This Technical Information sheet provides examples of using the PAL Immediate If function, IIF(), in calculated fields of forms or reports. Although the examples do not cover all possible uses, they will provide you with techniques which you could apply to your own applications. NOTE: You should know how to use PAL functions in reports and forms to understand all the material contained in this Technical Information sheet. You should also read the information in the sections of the following chapters from the Paradox manuals: PAL Reference, "IIF()" in PAL Commands and Functions PAL Programmers Guide, "IIF()" in Chapter 4 Paradox User's Guide, PAL Functions in Reports, in Chapter 8 Any other PAL functions referenced in this document are detailed in the PAL Reference manual. The syntax for the IIF() function is: IIF(Condition, ValueIfTrue, ValueIfFalse) where Condition is any expression that evaluates to a logical value of True or False; ValueIfTrue is the value returned if Condition evaluates to True; and ValueIfFalse is the value returned if Condition evaluates to False. The first eight examples take you through a credit card payment scenario using IIF() functions. The sample Credit database has the following structure and records: Credit │ Name │ Balance │ Limit │ Due Date │ Gender ───────┼──────┼──────────┼──────────┼──────────┼─────── │ Bob │ 1,100.00 │ 1,100.00 │ 12/02/91 │ M │ Judy │ 250.00 │ 3,000.00 │ 10/20/91 │ F │ Mark │ 12.00 │ 2,000.00 │ 6/12/92 │ m │ Pam │ 0.00 │ 2,500.00 │ │ f NOTE: The Balance and Limit fields have a field type of $. PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 2/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. Example 1: Suppose you wanted to show on a report or form the payment due date, but wanted to show the month value with leading zeros when the month is a single digit. You could do so by placing the following calculated field: IIF(LEN([Due Date])=7, "0"+STRVAL([Due Date]), [Due Date]) The LEN() function returns the length of an expression. Its syntax is LEN(Expression). The STRVAL() function converts any expression to a string. Its syntax is STRVAL(Expression). NOTE: When you place a calculated field that includes PAL functions, the field mask that appears is alphanumeric (as A's in the report). It is necessary to use the arrow keys to adjust the number of characters that the calculated field should display. In Example 1, adjust the calculated field to display 8 characters for date (i.e. MM/DD/YY). Do not be concerned! This is simply the way that Paradox displays these types of calculated fields. Explanation: If the length of the Due Date value is 7, the due date has a single digit month (for example, Mark's due date--2 characters each for the day and year, 1 character for the month, and 1 character for each slash). Thus, if you add a zero to the string value of the date the month value is now 2 characters. If the length of Due Date is already 8, then the month is a two digit number and the leading zero is not necessary. Result: Name IIF() Result ─────── ──────────── Bob 12/02/91 Judy 10/20/91 Mark 06/12/92 Pam Notice how Mark's due date now has 06 for the month. PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 3/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. Example 2: If a person has not yet reached their credit limit, indicate that they still have credit available, otherwise indicate that no credit remains: IIF([Balance]<[Limit], "Credit Available", "No Credit Available") Explanation: If the Balance is less than Limit, return "Credit Available", otherwise return "No Credit Available". Result: Name IIF() Result ─────── ─────────────────── Bob No Credit Available Judy Credit Available Mark Credit Available Pam Credit Available Notice that Bob is the only person who has used up his full credit limit (Balance = 1100.00, Limit = 1100.00). Example 3: If there is a credit card balance, show a minimum payment due (2% of the balance). Otherwise, show zero since there is no balance due: IIF([Balance]>0, [Balance]*.02, 0) Explanation: If the Balance is greater than zero, return 2% of the Balance value, otherwise return zero. PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 4/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. Result: Name IIF() Result ────── ──────────── Bob 22.00 Judy 5.00 Mark .24 Pam 0 Example 4: Paying 2% on any balance means that Mark would pay $0.24 and Judy would pay $5.00. In order to assure that a minimum payment of $20 is received, the previous IIF() expression could be modified like this: IIF([Balance]*.02<20, 20, [Balance]*.02) Explanation: If 2% of the balance is less than $20, require a payment of $20. Otherwise, since the balance is high enough such that a 2% payment is $20 or greater, require a 2% payment. Result: Name IIF() Result ─────── ──────────── Bob 22.00 Judy 20 Mark 20 Pam 20 Judy and Mark now pay a minimum of $20. NOTE: To format the output so that it looks like currency values, refer to Example 6 in this Technical Information sheet. PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 5/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. Example 5: But what about Mark? He has a balance of $12.00. The $20 minimum payment means he is expected to pay more than he owes. And Pam has a zero balance--she does not owe anything! The previous IIF() expression could be further modified so that anyone with a balance less than $20 pays it in full: IIF([Balance]<20, [Balance], IIF([Balance]*.02<20, 20, [Balance]*.02)) (The above expression must be entered on one line). Note: Notice how you can nest IIF() statements. Explanation: This nested IIF() expression states that if the balance is less than $20, the full balance is due. Otherwise, the balance is $20 or greater. So, if the balance is $20 or greater and 2% of this balance is less than $20, $20 is due. Otherwise, require a 2% payment of the balance (since this 2% payment will be at least $20). Result: Name IIF() Result ─────── ──────────── Bob 22.00 Judy 20 Mark 12.00 Pam 0 Now, Mark and Pam only pay what they owe. PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 6/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. Example 6: Finally, to format the output so that it looks like currency, embed the IIF() statement within a FORMAT() statement. FORMAT("W10.2, E$", IIF([Balance]<20, [Balance], IIF([Balance]*.02<20, 20, [Balance]*.02))) (The above expression must be entered on one line.) The FORMAT() function allows you to format a string. Its syntax is FORMAT(FormatSpec, String). Explanation: Take the resulting value of the IIF() statement and format it as 10 digits with 2 decimal places, and use a floating dollar sign. Result: Name IIF() Result ─────── ──────────── Bob $22.00 Judy $20.00 Mark $12.00 Pam $0.00 Example 7: Suppose you wanted to display the full gender of a cardholder based on the Gender field (which contains "M" for male and "F" for female). In order to display "Male" or "Female" in a report or form, you could use the following: IIF([Gender]="M", "Male", "Female") Explanation: If Gender is "M", return "Male", otherwise return "Female." PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 7/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. Result: Name Gender IIF() Result ────── ─────── ──────────── Bob M Male Judy F Female Mark m Female <----- Pam f Female Example 8: Notice that Mark's gender displayed as Female even though an "m" was entered. Since string comparisons are case-sensitive, "m" is not equivalent to "M" and, therefore, returns "Female". To assure a proper comparison when Gender values are lower case, use: IIF(UPPER([Gender])="M", "Male", "Female") The UPPER() function allows you to change a string to upper-case. Its syntax is UPPER(Expression). Explanation: Convert the Gender value to upper-case. Then, if it equals "M", return "Male", otherwise return "Female". Result: Gender IIF() Result ─────── ──────────── M Male F Female m Male f Female Since the UPPER() function converts "m" to "M", "Male" is returned for both "M" and "m." ┌──────────────────────────────────────────────────────────────┐ │The next set of examples no longer deal with the Credit table │ └──────────────────────────────────────────────────────────────┘ PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 8/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. Example 9: Suppose you had the following movie review table: Reviews │ Movie │ Critic 1 │ Critic 2 ──────────┼──────────────┼──────────────┼────────────── │ Car Wash │ 2 │ │ Fame │ │ 1 │ Splash │ 3 │ │ Top Gun │ │ 5 The movie review table contains reviews from two critics. Only one of the two critics reviews any single movie, never both. In order to represent the score with asterisks (using one asterisk per point), we could use the following expression: FILL("*", IIF(ISBLANK([Critic 1]), [Critic 2], [Critic 1])) The FILL() function allows you to repeat a character. Its syntax is FILL(Expression, Number). The ISBLANK() function tests whether an expression is blank or not. Its syntax is ISBLANK(Expression). Explanation: The IIF() function is used to return a value which will inform the FILL() function how many asterisks to display. If the Critic 1 field is blank, we know critic 2 reviewed the movie--return the value of the Critic 2 field. If the Critic 1 field is non-blank, we know critic 1 reviewed the movie--return the value of the Critic 1 field. Result: Movie LEN()/IIF() Result ─────── ────────────────── Car Wash ** Fame * Splash *** Top Gun ***** PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 9/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. Example 10: Suppose you had an employee table which had a DOB field for Date Of Birth. You could use the following expression to see if today was their birthday: IIF(MONTH([DOB])=MONTH(TODAY()) AND DAY([DOB])=DAY(TODAY()), "Happy Birthday!", "") (The above expression must be entered on one line.) The MONTH() function returns the numeric month value of a date. Its syntax is MONTH(Date). The DAY() function returns the numeric day value of a date. Its syntax is DAY(Date). Explanation: If the month value of the employee's date of birth is the same as the current month and the day value of the employee's date of birth is the same as the current day, then it is the employee's birthday--display message. Otherwise, it is not the employee's birthday--do not display anything. Note: Notice how you can use the "AND" operator to make a compound condition. Result: Assume today is 7/04/90. Employee ID DOB IIF() Result ─────────── ──────── ─────────────── 1 7/04/62 Happy Birthday! 2 12/25/43 3 7/04/57 Happy Birthday! 4 1/01/70 To order other Technical Information sheets by fax, phone our TechFax number at (800)822-4269. A menu system will guide you through ordering Technical Information sheets which will be faxed directly to you. You can even order a catalog which lists articles you can request when calling the TechFax Number. PRODUCT : Paradox NUMBER : 1112 VERSION : 4.0 OS : DOS DATE : August 25, 1992 PAGE : 10/10 TITLE : Various Examples Using the IIF (Immediate If) Function in Reports and Forms. DISCLAIMER: You have the right to use this technical information subject to the terms of the No-Nonsense License Statement that you received with the Borland product to which this information pertains.